import Database from 'better-sqlite3';
import path from 'path';

const DB_PATH = path.join(process.cwd(), 'data', 'delivery-management.db');

export interface DeviceTypeMapping {
  id?: number;
  classification_code: string;
  classification_name: string;
  english_name?: string;
  specialty: string;
  naming_table: string;
  position_table: string;
  base_params_table: string;
  tech_spec_table?: string;
  created_at?: string;
  updated_at?: string;
}

export interface DeviceParameterRecord {
  id?: number;
  device_id: number;
  table_name: string;
  record_data: Record<string, any>;
  created_at?: string;
  updated_at?: string;
}

export interface FieldMetadata {
  id?: number;
  table_name: string;
  table_code: string;
  table_description: string;
  field_sequence: number;
  field_name: string;
  field_column: string;
  example_description: string;
  data_type: string;
  measurement_class: string;
  data_source: string;
  is_required: boolean;
  created_at?: string;
  updated_at?: string;
}

export class DeviceParameterDatabase {
  private db: Database.Database;

  constructor() {
    this.db = new Database(DB_PATH, {
      // 设置超时
      timeout: parseInt(process.env?.DB_TIMEOUT || '5000'),
      // 只读模式下的并发访问优化
      readonly: false,
    });
    
    // 设置数据库优化参数
    this.db.pragma('journal_mode = WAL');
    this.db.pragma('cache_size = 1000');
    this.db.pragma('temp_store = memory');
    this.db.pragma('mmap_size = 268435456'); // 256MB
    this.db.pragma('busy_timeout = 5000');
  }

  // 获取设备类型映射
  getDeviceTypeMapping(classificationCode: string): DeviceTypeMapping | null {
    const stmt = this.db.prepare(`
      SELECT * FROM device_type_mappings 
      WHERE classification_code = ?
    `);
    return stmt.get(classificationCode) as DeviceTypeMapping | null;
  }

  // 获取所有设备类型映射
  getAllDeviceTypeMappings(): DeviceTypeMapping[] {
    const stmt = this.db.prepare(`
      SELECT * FROM device_type_mappings
      ORDER BY specialty, classification_name
    `);
    return stmt.all() as DeviceTypeMapping[];
  }

  // 获取所有设备
  getAllDevices(): any[] {
    const stmt = this.db.prepare('SELECT * FROM devices ORDER BY specialty, name');
    return stmt.all();
  }

  // 根据专业获取设备类型映射
  getDeviceTypeMappingsBySpecialty(specialty: string): DeviceTypeMapping[] {
    const stmt = this.db.prepare(`
      SELECT * FROM device_type_mappings 
      WHERE specialty = ?
      ORDER BY classification_name
    `);
    return stmt.all(specialty) as DeviceTypeMapping[];
  }

  // 获取参数表的字段定义（从元数据表，如果不存在则从实际表结构）
  getParameterTableFields(tableName: string): FieldMetadata[] {
    try {
      const stmt = this.db.prepare(`
        SELECT * FROM parameter_field_metadata
        WHERE table_name = ?
        ORDER BY field_sequence
      `);
      const metadata = stmt.all(tableName) as FieldMetadata[];
      
      // 如果有元数据，直接返回
      if (metadata && metadata.length > 0) {
        return metadata;
      }
    } catch (error) {
      console.log(`元数据表不存在或查询失败，使用实际表结构: ${tableName}`);
    }
    
    // 如果没有元数据，从实际表结构生成字段信息
    try {
      const tableInfo = this.db.prepare(`PRAGMA table_info(${tableName})`).all() as any[];
      
      return tableInfo
        .filter(col => !['id', 'created_at', 'updated_at'].includes(col.name)) // 排除系统字段
        .map((col, index) => ({
          id: index + 1,
          table_name: tableName,
          table_code: tableName.replace('table_c', 'C.'),
          table_description: `${tableName}参数表`,
          field_sequence: index + 1,
          field_name: col.name,
          field_column: col.name,
          example_description: '',
          data_type: col.type === 'REAL' ? '数值型' : '文本型',
          measurement_class: '',
          data_source: 'E', // 默认工程数据源
          is_required: col.notnull === 1,
          created_at: new Date().toISOString(),
          updated_at: new Date().toISOString()
        } as FieldMetadata));
    } catch (error) {
      console.error(`无法获取表 ${tableName} 的结构:`, error);
      return [];
    }
  }

  // 获取参数表的基本字段信息（兼容旧接口）
  getParameterTableFieldsBasic(tableName: string): Array<{name: string, type: string}> {
    const metadata = this.getParameterTableFields(tableName);
    return metadata.map(field => ({
      name: field.field_name,
      type: field.data_type === '数值型' ? 'REAL' : 'TEXT'
    }));
  }

  // 为设备创建参数记录
  createParameterRecord(deviceId: number, tableName: string, data: Record<string, any>): number {
    console.log('=== DeviceParameterDatabase.createParameterRecord ===');
    console.log('设备ID:', deviceId, '表名:', tableName);
    console.log('输入数据:', data);
    
    const now = new Date().toISOString();

    // 获取表字段元数据
    const fieldMetadata = this.getParameterTableFields(tableName);
    const metadataFieldNames = fieldMetadata.map(f => f.field_name);
    console.log('元数据字段名列表:', metadataFieldNames);
    
    // 将元数据字段名映射到实际列名（处理 "/" 到 "_" 的转换）
    const actualFieldNames = metadataFieldNames.map(fieldName => 
      fieldName.replace(/\//g, '_')  // 将 "/" 替换为 "_"
    );
    console.log('实际列名列表:', actualFieldNames);

    // 构建插入语句（使用实际列名）
    const quotedFieldNames = actualFieldNames.map(name => `[${name}]`); // 用方括号包围字段名
    const columns = ['[created_at]', '[updated_at]', ...quotedFieldNames];
    const placeholders = columns.map(() => '?').join(', ');
    
    // 清理数据中可能导致SQL问题的字符
    const cleanValue = (value: any) => {
      if (value === null || value === undefined) return null;
      if (typeof value === 'string') {
        // 移除可能导致SQL语法错误的字符
        return value.replace(/[\\"']/g, '');
      }
      return value;
    };
    
    // 使用元数据字段名从数据中获取值（因为导入数据使用的是元数据字段名）
    const values = [now, now, ...metadataFieldNames.map(name => cleanValue(data[name]))];

    // 验证表名是否有效（允许字母、数字、下划线、中文字符）
    if (!/^[a-zA-Z_\u4e00-\u9fff][a-zA-Z0-9_\u4e00-\u9fff]*$/.test(tableName)) {
      throw new Error(`无效的表名: ${tableName}`);
    }
    
    const insertSQL = `
      INSERT INTO [${tableName}] (${columns.join(', ')})
      VALUES (${placeholders})
    `;

    console.log('生成的SQL:', insertSQL);
    console.log('参数值:', values);
    console.log('字段映射 (元数据 -> 实际列名):');
    metadataFieldNames.forEach((metaName, index) => {
      console.log(`  ${metaName} -> ${actualFieldNames[index]}`);
    });

    // 检查参数值中是否包含可能导致问题的字符
    values.forEach((value, index) => {
      if (typeof value === 'string' && value.includes('/')) {
        console.log(`参数 ${index} 包含斜杠:`, value);
      }
    });

    try {
      const stmt = this.db.prepare(insertSQL);
      const result = stmt.run(...values);
      console.log('参数记录创建成功，ID:', result.lastInsertRowid);
      return result.lastInsertRowid as number;
    } catch (error) {
      console.error('createParameterRecord SQL执行失败:', error);
      console.error('失败的SQL:', insertSQL);
      console.error('失败的参数:', values);
      throw error;
    }
  }

  // 更新参数记录
  updateParameterRecord(tableName: string, recordId: number, data: Record<string, any>): void {
    const now = new Date().toISOString();

    // 获取表字段元数据
    const fieldMetadata = this.getParameterTableFields(tableName);
    const fieldNames = fieldMetadata.map(f => f.field_name);

    // 构建更新语句
    const setClause = fieldNames.map(name => `${name} = ?`).join(', ');
    const values = [...fieldNames.map(name => data[name] || null), now, recordId];

    const updateSQL = `
      UPDATE ${tableName}
      SET ${setClause}, updated_at = ?
      WHERE id = ?
    `;

    const stmt = this.db.prepare(updateSQL);
    stmt.run(...values);
  }

  // 获取参数记录
  getParameterRecord(tableName: string, recordId: number): Record<string, any> | null {
    const stmt = this.db.prepare(`SELECT * FROM ${tableName} WHERE id = ?`);
    return stmt.get(recordId) as Record<string, any> | null;
  }

  // 删除参数记录
  deleteParameterRecord(tableName: string, recordId: number): void {
    const stmt = this.db.prepare(`DELETE FROM ${tableName} WHERE id = ?`);
    stmt.run(recordId);
  }

  // 更新设备的参数记录关联
  updateDeviceParameterReferences(deviceId: number, updates: {
    naming_record_id?: number;
    position_record_id?: number;
    base_params_record_id?: number;
    tech_spec_record_id?: number;
  }): void {
    const now = new Date().toISOString();
    const setFields = [];
    const values = [];
    
    if (updates.naming_record_id !== undefined) {
      setFields.push('naming_record_id = ?');
      values.push(updates.naming_record_id);
    }
    if (updates.position_record_id !== undefined) {
      setFields.push('position_record_id = ?');
      values.push(updates.position_record_id);
    }
    if (updates.base_params_record_id !== undefined) {
      setFields.push('base_params_record_id = ?');
      values.push(updates.base_params_record_id);
    }
    if (updates.tech_spec_record_id !== undefined) {
      setFields.push('tech_spec_record_id = ?');
      values.push(updates.tech_spec_record_id);
    }
    
    if (setFields.length === 0) return;
    
    setFields.push('updated_at = ?');
    values.push(now, deviceId);
    
    const updateSQL = `
      UPDATE devices 
      SET ${setFields.join(', ')}
      WHERE id = ?
    `;
    
    const stmt = this.db.prepare(updateSQL);
    stmt.run(...values);
  }

  // 获取设备的完整参数信息
  getDeviceWithParameters(deviceId: number): {
    device: any;
    mapping: DeviceTypeMapping | null;
    parameters: {
      naming?: Record<string, any>;
      position?: Record<string, any>;
      base_params?: Record<string, any>;
      tech_spec?: Record<string, any>;
    };
  } | null {
    // 获取设备信息
    const deviceStmt = this.db.prepare('SELECT * FROM devices WHERE id = ?');
    const device = deviceStmt.get(deviceId);
    
    if (!device) return null;
    
    // 获取设备类型映射
    const mapping = this.getDeviceTypeMapping(device.classification_code);
    
    if (!mapping) {
      return { device, mapping: null, parameters: {} };
    }
    
    // 获取参数记录
    const parameters: any = {};
    
    if (device.naming_record_id && mapping.naming_table) {
      parameters.naming = this.getParameterRecord(mapping.naming_table, device.naming_record_id);
    }
    
    if (device.position_record_id && mapping.position_table) {
      parameters.position = this.getParameterRecord(mapping.position_table, device.position_record_id);
    }
    
    if (device.base_params_record_id && mapping.base_params_table) {
      parameters.base_params = this.getParameterRecord(mapping.base_params_table, device.base_params_record_id);
    }
    
    if (device.tech_spec_record_id && mapping.tech_spec_table) {
      parameters.tech_spec = this.getParameterRecord(mapping.tech_spec_table, device.tech_spec_record_id);
    }
    
    return { device, mapping, parameters };
  }

  // 为设备创建完整的参数记录
  createDeviceParameters(deviceId: number, parameterData: {
    naming?: Record<string, any>;
    position?: Record<string, any>;
    base_params?: Record<string, any>;
    tech_spec?: Record<string, any>;
  }): void {
    // 获取设备信息
    const deviceStmt = this.db.prepare('SELECT * FROM devices WHERE id = ?');
    const device = deviceStmt.get(deviceId);
    
    if (!device) throw new Error(`Device with id ${deviceId} not found`);
    
    // 获取设备类型映射
    const mapping = this.getDeviceTypeMapping(device.classification_code);
    if (!mapping) throw new Error(`No mapping found for classification code ${device.classification_code}`);
    
    const updates: any = {};
    
    // 创建命名参数记录
    if (parameterData.naming && mapping.naming_table) {
      const recordId = this.createParameterRecord(deviceId, mapping.naming_table, parameterData.naming);
      updates.naming_record_id = recordId;
    }
    
    // 创建位置参数记录
    if (parameterData.position && mapping.position_table) {
      const recordId = this.createParameterRecord(deviceId, mapping.position_table, parameterData.position);
      updates.position_record_id = recordId;
    }
    
    // 创建基础参数记录
    if (parameterData.base_params && mapping.base_params_table) {
      const recordId = this.createParameterRecord(deviceId, mapping.base_params_table, parameterData.base_params);
      updates.base_params_record_id = recordId;
    }
    
    // 创建技术规格记录
    if (parameterData.tech_spec && mapping.tech_spec_table) {
      const recordId = this.createParameterRecord(deviceId, mapping.tech_spec_table, parameterData.tech_spec);
      updates.tech_spec_record_id = recordId;
    }
    
    // 更新设备的参数记录引用
    this.updateDeviceParameterReferences(deviceId, updates);
  }

  // 获取表的元数据信息
  getTableMetadata(tableName: string): {
    table_name: string;
    table_code: string;
    short_name: string;
    table_description: string;
    field_count?: number;
  } | null {
    // 首先从 parameter_table_metadata 获取表的基本信息
    const metaStmt = this.db.prepare(`
      SELECT table_name, table_code, short_name, description as table_description
      FROM parameter_table_metadata
      WHERE table_name = ?
    `);
    const metadata = metaStmt.get(tableName) as any;
    
    if (!metadata) {
      return null;
    }
    
    // 然后从 parameter_field_metadata 获取字段数量
    const countStmt = this.db.prepare(`
      SELECT COUNT(*) as field_count
      FROM parameter_field_metadata
      WHERE table_name = ?
    `);
    const countResult = countStmt.get(tableName) as any;
    
    return {
      ...metadata,
      field_count: countResult?.field_count || 0
    };
  }

  // 根据数据来源获取字段
  getFieldsByDataSource(tableName: string, dataSource: string): FieldMetadata[] {
    const stmt = this.db.prepare(`
      SELECT * FROM parameter_field_metadata
      WHERE table_name = ? AND data_source = ?
      ORDER BY field_sequence
    `);
    return stmt.all(tableName, dataSource) as FieldMetadata[];
  }

  // 获取必填字段
  getRequiredFields(tableName: string): FieldMetadata[] {
    const stmt = this.db.prepare(`
      SELECT * FROM parameter_field_metadata
      WHERE table_name = ? AND is_required = 1
      ORDER BY field_sequence
    `);
    return stmt.all(tableName) as FieldMetadata[];
  }

  // 根据计量类获取字段
  getFieldsByMeasurementClass(tableName: string, measurementClass: string): FieldMetadata[] {
    const stmt = this.db.prepare(`
      SELECT * FROM parameter_field_metadata
      WHERE table_name = ? AND measurement_class = ?
      ORDER BY field_sequence
    `);
    return stmt.all(tableName, measurementClass) as FieldMetadata[];
  }

  // 获取所有计量类列表
  getAllMeasurementClasses(): string[] {
    const stmt = this.db.prepare(`
      SELECT DISTINCT measurement_class
      FROM parameter_field_metadata
      WHERE measurement_class != '—' AND measurement_class != ''
      ORDER BY measurement_class
    `);
    const results = stmt.all() as Array<{measurement_class: string}>;
    return results.map(r => r.measurement_class);
  }

  // 获取所有数据来源列表
  getAllDataSources(): string[] {
    const stmt = this.db.prepare(`
      SELECT DISTINCT data_source
      FROM parameter_field_metadata
      WHERE data_source != ''
      ORDER BY data_source
    `);
    const results = stmt.all() as Array<{data_source: string}>;
    return results.map(r => r.data_source);
  }

  // 获取设备参数录入统计
  getDeviceParameterStats(): {
    totalDevices: number;
    enteredDevices: number;
    validatedDevices: number;
    specialtyStats: Array<{
      specialty: string;
      totalDevices: number;
      enteredDevices: number;
      validatedDevices: number;
      entryRate: number;
      validationRate: number;
    }>;
  } {
    // 获取所有设备
    const allDevices = this.getAllDevices();

    // 统计已录入参数的设备（至少有一个参数记录ID不为空）
    const enteredDevices = allDevices.filter(device =>
      device.naming_record_id ||
      device.position_record_id ||
      device.base_params_record_id ||
      device.tech_spec_record_id
    );

    // 统计校验通过的设备（这里假设有完整的基础参数就算校验通过）
    const validatedDevices = allDevices.filter(device =>
      device.base_params_record_id &&
      device.naming_record_id
    );

    // 按专业统计
    const specialtyMap = new Map<string, {
      total: any[];
      entered: any[];
      validated: any[];
    }>();

    allDevices.forEach(device => {
      const specialty = device.specialty;
      if (!specialtyMap.has(specialty)) {
        specialtyMap.set(specialty, { total: [], entered: [], validated: [] });
      }
      specialtyMap.get(specialty)!.total.push(device);
    });

    enteredDevices.forEach(device => {
      const specialty = device.specialty;
      if (specialtyMap.has(specialty)) {
        specialtyMap.get(specialty)!.entered.push(device);
      }
    });

    validatedDevices.forEach(device => {
      const specialty = device.specialty;
      if (specialtyMap.has(specialty)) {
        specialtyMap.get(specialty)!.validated.push(device);
      }
    });

    const specialtyStats = Array.from(specialtyMap.entries()).map(([specialty, data]) => {
      const totalCount = data.total.length;
      const enteredCount = data.entered.length;
      const validatedCount = data.validated.length;

      return {
        specialty,
        totalDevices: totalCount,
        enteredDevices: enteredCount,
        validatedDevices: validatedCount,
        entryRate: totalCount > 0 ? Math.round((enteredCount / totalCount) * 100) : 0,
        validationRate: enteredCount > 0 ? Math.round((validatedCount / enteredCount) * 100) : 0
      };
    });

    return {
      totalDevices: allDevices.length,
      enteredDevices: enteredDevices.length,
      validatedDevices: validatedDevices.length,
      specialtyStats
    };
  }

  close(): void {
    this.db.close();
  }
}

// 单例实例
let dbInstance: DeviceParameterDatabase | null = null;

export function getDeviceParameterDb(): DeviceParameterDatabase {
  if (!dbInstance) {
    dbInstance = new DeviceParameterDatabase();
  }
  return dbInstance;
}
